﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.ComponentModel
Public Class Ofertas
    Inherits BDDatos
    Public TITULO As String
    Public DESCRIPCION As String
    Public SALARIO As Decimal
    Public DURACION As String
    Public COMIENZO As String
    Public TIPO_EMPLEO As String
    Public VACANTES As String
    Public IDPK As String
    Public PROG As String
    Public TIP_CONVENIO As String
    Sub New()
        Me.Tabla = "Ofertas"
        Me.Vista = "VOfertas"
    End Sub
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetOferProg(ByVal ID_OFERTA As String) As DataTable
        Me.Conectar()
        querystring = "SELECT PROGRAMAS.ID, PROGRAMAS.COD_FACULTAD, PROGRAMAS.NOM_PROGRAMA, (SELECT CASE WHEN COUNT (*) = 0 THEN '0' ELSE '1' END AS EST FROM OFERPROG WHERE     ID_OFERTA = :ID_OFERTA AND cod_programa = PROGRAMAS.Id) EST FROM PROGRAMAS ORDER BY ID"
        CrearComando(querystring)
        AsignarParametroCadena(":ID_OFERTA", ID_OFERTA)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt

    End Function
    Public Function Update() As String
        Me.Conectar()
        Me.ComenzarTransaccion()
        Try
            If PROG = "" Then
                Throw New Exception("Debe seleccionar al menos un programa para la Oferta")
            End If
            Me.querystring = "Update OFERTAS Set " & _
            "TITULO=:TITULO, DESCRIPCION=:DESCRIPCION, SALARIO=:SALARIO, DURACION=:DURACION, COMIENZO=:COMIENZO, " & _
            "TIPO_EMPLEO=:TIPO_EMPLEO, VACANTES=:VACANTES, ID_EMPRESA=:ID_EMPRESA, PERFILES=:PERFILES, TIP_CONVENIO=:TIP_CONVENIO WHERE ID_OFERTA=:ID_OFERTA"
            Me.CrearComando(querystring)
            Me.AsignarParametroCadena(":TITULO", UCase(Me.TITULO))
            Me.AsignarParametroCadena(":DESCRIPCION", UCase(Me.DESCRIPCION))
            Me.AsignarParametroCadena(":SALARIO", UCase(Me.SALARIO))
            Me.AsignarParametroCadena(":DURACION", UCase(Me.DURACION))
            Me.AsignarParametroCadena(":COMIENZO", Me.COMIENZO)
            Me.AsignarParametroCadena(":TIPO_EMPLEO", Me.TIPO_EMPLEO)
            Me.AsignarParametroCadena(":VACANTES", Me.VACANTES)
            Me.AsignarParametroCadena(":ID_EMPRESA", Me.usuario)
            Me.AsignarParametroCadena(":PERFILES", PROG)
            Me.AsignarParametroCadena(":TIP_CONVENIO", TIP_CONVENIO)
            Me.AsignarParametroCadena(":ID_OFERTA", Me.IDPK)
            Me.num_reg = EjecutarComando()

            '-----------------RELACIONAR PROGRAMAS A OFERTA--------------------------------------------
            'querystring = "DELETE FROM OFERPROG WHERE Id_Oferta='" + PRO_SEL_NRO + "' and Grupo=" + Grupo
            'Me.CrearComando(querystring)
            'EjecutarComando()

            'For i = 0 To nm
            '    querystring = "INSERT INTO PConMun(Cod_Mun,nro_pcon, Grupo)VALUES('" + Mun(i) + "','" + PRO_SEL_NRO + "'," + Grupo + ") "
            '    Me.CrearComando(querystring)
            '    EjecutarComando()
            'Next i

            '-----------------------------------------------------------------------------------------------
            Me.Msg = "Se realizo la Operacion Exitosamente - " + num_reg.ToString + " Filas Afectadas"
            Me.lErrorG = False
            Me.ConfirmarTransaccion()
        Catch ex As Exception
            CancelarTransaccion()
            Me.lErrorG = True
            Me.Msg = "Err: " + ex.Message
        Finally
            Me.Desconectar()
        End Try
        Return Msg
    End Function
    Public Function Insert() As String
        Me.Conectar()
        Me.ComenzarTransaccion()
        Try
            If PROG = "" Then
                Throw New Exception("Debe seleccionar al menos un programa para la Oferta")
            End If
            Me.querystring = "Insert into OFERTAS" & _
            "(TITULO, DESCRIPCION, SALARIO, DURACION, COMIENZO, TIPO_EMPLEO, VACANTES, ID_EMPRESA,PERFILES,TIP_CONVENIO)" & _
            "Values" & _
            "(:TITULO, :DESCRIPCION, :SALARIO, :DURACION, :COMIENZO, :TIPO_EMPLEO, :VACANTES, :ID_EMPRESA,:PERFILES,:TIP_CONVENIO)"
            Me.CrearComando(querystring)
            Me.AsignarParametroCadena(":TITULO", UCase(Me.TITULO))
            Me.AsignarParametroCadena(":DESCRIPCION", UCase(Me.DESCRIPCION))
            Me.AsignarParametroCadena(":SALARIO", UCase(Me.SALARIO))
            Me.AsignarParametroCadena(":DURACION", UCase(Me.DURACION))
            Me.AsignarParametroCadena(":COMIENZO", Me.COMIENZO)
            Me.AsignarParametroCadena(":TIPO_EMPLEO", Me.TIPO_EMPLEO)
            Me.AsignarParametroCadena(":VACANTES", Me.VACANTES)
            Me.AsignarParametroCadena(":ID_EMPRESA", Me.usuario)
            Me.AsignarParametroCadena(":PERFILES", PROG)
            Me.AsignarParametroCadena(":TIP_CONVENIO", TIP_CONVENIO)
            Me.num_reg = EjecutarComando()

            '-----------------RELACIONAR PROGRAMAS A OFERTA--------------------------------------------
            'querystring = "DELETE FROM OFERPROG WHERE Id_Oferta='" + PRO_SEL_NRO + "' and Grupo=" + Grupo
            'Me.CrearComando(querystring)
            'EjecutarComando()

            'For i = 0 To nm
            '    querystring = "INSERT INTO PConMun(Cod_Mun,nro_pcon, Grupo)VALUES('" + Mun(i) + "','" + PRO_SEL_NRO + "'," + Grupo + ") "
            '    Me.CrearComando(querystring)
            '    EjecutarComando()
            'Next i

            '-----------------------------------------------------------------------------------------------
            Me.Msg = "Se realizo la Operacion Exitosamente - " + num_reg.ToString + " Filas Afectadas"
            Me.lErrorG = False
            Me.ConfirmarTransaccion()
        Catch ex As Exception
            CancelarTransaccion()
            Me.lErrorG = True
            Me.Msg = "Err: " + ex.Message
        Finally
            Me.Desconectar()
        End Try
        Return Msg
    End Function
    Public Function PubOferta(ByVal Id_Oferta As String, ByVal Estado As String) As String
        Me.Conectar()
        Try
            Me.querystring = "Update Ofertas set Estado=:Estado where Id_Oferta=:Id_Oferta"
            CrearComando(querystring)
            Me.AsignarParametroCadena(":Id_Oferta", Id_Oferta)
            Me.AsignarParametroCadena(":Estado", Estado)
            Me.num_reg = EjecutarComando()
            Me.lErrorG = False
            Me.Msg = MsgOk
        Catch ex As Exception
            Me.lErrorG = True
            Me.Msg = "Error: " & ex.Message
        Finally
            Me.Desconectar()
        End Try
        Return Msg
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetxEmpresa() As DataTable
        Me.Conectar()
        Me.querystring = "Select * from VOFERTAS where Id_Empresa=:cedula"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":cedula", Me.usuario)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetxEmpresaGes() As DataTable
        Me.Conectar()
        Me.querystring = "Select * from VOFERTAS where Id_Empresa=:cedula and Estado='PU'"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":cedula", Me.usuario)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetxEmpresa(ByVal Empresa As String) As DataTable
        Me.Conectar()
        Me.querystring = "Select * from VOFERTAS where Id_Empresa=:cedula"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":cedula", Empresa)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetxEmpresaPub(ByVal Empresa As String) As DataTable
        Me.Conectar()
        Me.querystring = "Select * from VOFERTAS where Id_Empresa=:cedula And Estado='PU'"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":cedula", Empresa)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetxUsuario() As DataTable
        Me.Conectar()
        Me.querystring = "SELECT * FROM VOFERTAS WHERE (SELECT COD_PROGRAMA FROM EGRESADOS WHERE CEDULA=:cedula) IN (SELECT COD_PROGRAMA FROM OFERPROG WHERE ID_OFERTA=VOFERTAS.ID_OFERTA) AND VOFERTAS.ID_OFERTA NOT IN (SELECT ID_OFERTA FROM OFERTA_ESTUDIANTE WHERE ID_ESTUDIANTE=:cedula) AND VOFERTAS.ESTADO='PU'"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":cedula", Me.usuario)
        Me.AsignarParametroCadena(":cedula", Me.usuario)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetbyPK(ByVal Cedula As String) As DataTable
        Me.Conectar()
        Me.querystring = "Select * from VOFERTAS where ID_OFERTA=:cedula"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":cedula", Cedula)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetbyOferta(ByVal Oferta As String) As DataTable
        Me.Conectar()
        Me.querystring = "Select * from VOFERTA_ESTUDIANTE where ID_OFERTA=:Oferta"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":Oferta", Oferta)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetbyOfertaGes(ByVal Oferta As String) As DataTable
        Me.Conectar()
        Me.querystring = "Select * from VOFERTA_ESTUDIANTE where ID_OFERTA=:Oferta and Estado<>'PO'"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":Oferta", Oferta)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetOfertabyEval(ByVal Oferta As String) As DataTable
        Me.Conectar()
        Me.querystring = "Select * from VOFERTA_ESTUDIANTE where ID=:Oferta"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":Oferta", Oferta)
        'Throw New Exception(vComando.CommandText)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetOfertaNotEval(ByVal Oferta As String, ByVal tip_eval As String) As DataTable
        Me.Conectar()
        Me.querystring = "Select * from veval_oferta where ID_OFER_EST=:Oferta and TIP_EVAL=:TIP_EVAL"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":Oferta", Oferta)
        Me.AsignarParametroCadena(":TIP_EVAL", tip_eval)
        'Throw New Exception(vComando.CommandText)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetOfertabyConEval(ByVal Cedula As String) As DataTable
        Me.Conectar()
        Me.querystring = "Select * from VOFERTA_ESTUDIANTe where id_estudiante=:Oferta and estado='AC'"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":Oferta", Cedula)
        'Throw New Exception(vComando.CommandText)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    <DataObjectMethodAttribute(DataObjectMethodType.Select, True)> _
    Public Function GetOferEvalbyEmpresa() As DataTable
        Me.Conectar()
        Me.querystring = "Select * from VOFERTA_ESTUDIANTE where ID_EMPRESA=:Empresa and Estado='AC'"
        CrearComando(querystring)
        Me.AsignarParametroCadena(":Empresa", Me.usuario)
        Dim dt As DataTable = EjecutarConsultaDataTable()
        Me.Desconectar()
        Return dt
    End Function
    Public Function Aplicar(ByVal Id_Oferta As String) As String
        Try
            Me.Conectar()
            Me.querystring = "INSERT INTO OFERTA_ESTUDIANTE(ID_OFERTA,ID_ESTUDIANTE) values (:ID_OFERTA,:ID_ESTUDIANTE)"
            Me.CrearComando(querystring)
            Me.AsignarParametroCadena(":ID_OFERTA", Id_Oferta)
            Me.AsignarParametroCadena(":ID_ESTUDIANTE", Me.usuario)
            Me.num_reg = EjecutarComando()
            Me.lErrorG = False
            Me.Msg = MsgOk
        Catch ex As Exception
            Me.lErrorG = True
            Me.Msg = "Error: " + ex.Message
        Finally
            Me.Desconectar()
        End Try
        Return Msg
    End Function
    Public Function Confirmar(ByVal estado As String, ByVal promedio As Decimal, ByVal id As String) As String
        Try
            Me.Conectar()
            Me.querystring = "UPDATE OFERTA_ESTUDIANTE SET ESTADO=:ESTADO, PROMEDIO=:PROMEDIO WHERE ID=:ID"
            Me.CrearComando(querystring)
            Me.AsignarParametroCadena(":ESTADO", estado)
            Me.AsignarParametroCadena(":PROMEDIO", promedio)
            Me.AsignarParametroDecimal(":ID", id)
            Me.num_reg = EjecutarComando()
            Me.lErrorG = False
            Me.Msg = MsgOk
        Catch ex As Exception
            Me.lErrorG = True
            Me.Msg = "Error: " + ex.Message
        Finally
            Me.Desconectar()
        End Try
        Return Msg
    End Function
    Public Function Aceptar(ByVal Inicio As String, ByVal Culminacion As String, ByVal Cargo As String, ByVal id As String) As String
        Try
            Me.Conectar()
            Me.querystring = "UPDATE OFERTA_ESTUDIANTE SET ESTADO='AC', INICIO=:INICIO, CULMINACION=:CULMINACION, CARGO=:CARGO WHERE ID=:ID"
            Me.CrearComando(querystring)
            Me.AsignarParametroCadena(":INICIO", Inicio)
            Me.AsignarParametroCadena(":CULMINACION", Culminacion)
            Me.AsignarParametroCadena(":ID", id)
            Me.AsignarParametroCadena(":CARGO", Cargo)
            'Throw New Exception(vComando.CommandText)
            Me.num_reg = EjecutarComando()
            Me.lErrorG = False
            Me.Msg = MsgOk
        Catch ex As Exception
            Me.lErrorG = True
            Me.Msg = "Error: " + ex.Message
        Finally
            Me.Desconectar()
        End Try
        Return Msg
    End Function
End Class